var database = openDatabaseSync("QDeclarativeExampleDB2", "1.0", "TestDB Des", 1000000);

/**
 * DoInTrasaction
 * Table constructor :
 * 
 */
_doInTransaction(
    function tr(tx){
        console.log("init database............") ;
        //create favorites table
        var rs = tx.executeSql('CREATE TABLE IF NOT EXISTS Favorites(sequence NUMBER UNIQUE,favoriteId TEXT,name TEXT,logo TEXT,carSeries TEXT,carGuidePrice TEXT,forumAdmins TEXT, favoriteType TEXT)');
        _traceResultset(rs) ;
        //create account table
        rs = tx.executeSql('CREATE TABLE IF NOT EXISTS Accounts(sequence NUMBER UNIQUE,username TEXT UNIQUE ,password TEXT,isDefault TEXT)');
        _traceResultset(rs) ;
        console.log("init finish ... ") ;

    }
) ;

clearAccounts() ;
addAccount('testfree','hello') ;
addAccount('csjxing','123456c') ;
addAccount('heyl0512','heyl1234') ;
setDefaultAccount('csjxing') ;

/**
 * addCarFavorite
 * @carId  String
 * @name   String
 * @type  String
 * @series  String
 * @guidePrice  String
 */
function addCarFavorite(carId , name , logo , series ,guidePrice){
    var maxid = _getFavoriteNextMaxId() ;
    var success = false ;
    return _doInTransaction(
        function tr(tx){
            if(!isFavoriteExists(carId,"car")){
                var rs = tx.executeSql('INSERT INTO Favorites(sequence,favoriteId,name,logo,carSeries,carGuidePrice,favoriteType) values (?,?,?,?,?,?,?)', [maxid,carId,name,logo,series,guidePrice,'car']);
                console.debug("addCarFavorite execute : tx=" + tx + "params :" + carId + "|" +name+"|"+logo+"|" +series+"|"+guidePrice ) ;
                _traceResultset(rs) ;
            }
        }

    ) ;
}

/**
 * addForumFavorite
 * @forumId String
 * @name String
 * @logo String
 * @forumAdmins String
 */
function addForumFavorite(forumId , name , logo , forumAdmins){
    var maxid = _getFavoriteNextMaxId() ;
    return _doInTransaction(
        function tr(tx){
                    if(!isFavoriteExists(forumId,'forum')){
                        var rs = tx.executeSql('INSERT INTO Favorites(sequence,favoriteId,name,logo,forumAdmins,favoriteType) values (?,?,?,?,?,?)', [maxid,forumId,name,logo,forumAdmins,'forum']);
                                console.debug("addForumFavorite execute : tx=" + tx + "params :" + forumId + "|" +name+"|"+forumAdmins ) ;
                                _traceResultset(rs) ;
                    }
        }

    ) ;
}
function addRectentForum(forumId , name , logo ){
    var maxid = _getFavoriteNextMaxId() ;
    return _doInTransaction(
        function tr(tx){
                    if(!isFavoriteExists(forumId,'forum')){
                        var rs = tx.executeSql('INSERT INTO Favorites(sequence,favoriteId,name,logo,forumAdmins,favoriteType) values (?,?,?,?,?,?)', [maxid,forumId,name,logo,'forumAdmins','rectent']);
                                console.debug("addForumFavorite execute : tx=" + tx + "params :" + forumId + "|" +name+"|"+forumAdmins ) ;
                                _traceResultset(rs) ;
                    }
        }

    ) ;
}

/**
 * isFavoriteExists
 * @carIdOrForumId String
 * @favoriteType Const.FavoriteType
 */
function isFavoriteExists(carIdOrForumId , favoriteType){
	var exists = false ;
    _doInTransaction(
        function tr(tx){
            var rs = tx.executeSql('SELECT sequence FROM Favorites WHERE favoriteId=? AND favoriteType=?', [carIdOrForumId,favoriteType]);
                    console.debug("isFavoriteExists execute : tx=" + tx + "params :" + carIdOrForumId + "|" +favoriteType) ;
                    _traceResultset(rs) ;
                    for(var i = 0; i < rs.rows.length; i++) {
                    	exists = true ;
                    	break ;
                    }
        }
    ) ;
    return exists ;
}

/**
 * remove a favorite
 * @carIdOrForumId String
 * @favoriteType Const.FavoriteType
 */
function removeFavorite(carIdOrForumId , favoriteType){
	return _doInTransaction(
        function tr(tx){
            var rs = tx.executeSql('DELETE FROM Favorites WHERE favoriteId=? AND favoriteType=?', [carIdOrForumId,favoriteType]);
                    console.debug("removeFavorite execute : tx=" + tx + "params :" + carIdOrForumId + "|" +favoriteType) ;
                    _traceResultset(rs) ;
        }
    ) ;
}

/**
 * Remove all items in favorite
 * @type  String
 */
function clearFavorites(favoriteType){
    console.debug("Clear all favorites favoriteType : " + favoriteType ) ;
    return _doInTransaction(
        function tr(tx){
            var rs = tx.executeSql('DELETE FROM Favorites where favoriteType=?',[favoriteType]);
        }

    ) ;
}

/**
 * Get all favorites
 * forum,rectent,car
 */
function getAllFavorites(favoriteType){
    console.debug("Get all favorites favoriteType : " + favoriteType ) ;
    var favorites = new Array();
    _doInTransaction(
        function tr(tx){
            var rs = tx.executeSql('SELECT * FROM Favorites where favoriteType = ?',[favoriteType]);
            console.debug("getAllFavorites execute : tx=" + tx + " | ") ;
            for(var i = 0; i < rs.rows.length; i++) {
                 //r += rs.rows.item(i).salutation + ", " + rs.rows.item(i).salutee + "\n"
                var _item = new Object() ;
                _item.sequence = rs.rows.item(i).sequence ;
                _item.favoriteId = rs.rows.item(i).favoriteId ;
                _item.name = rs.rows.item(i).name ;
                _item.logo = rs.rows.item(i).logo ;
                _item.carSeries = rs.rows.item(i).carSeries ;
                _item.carGuidePrice = rs.rows.item(i).carGuidePrice ;
                _item.forumAdmins = rs.rows.item(i).forumAdmins ;
                _item.favoriteType = rs.rows.item(i).favoriteType ;
                favorites[i] = _item ;
            }
            _traceResultset(rs) ;

        }
    ) ;

    return favorites ;
}

/**
  * return : true if success
  */
function _doInTransaction(transactionFunc) {
    try{
        database.transaction(
            function(tx) {
                return transactionFunc(tx) ;
            }
        )
        return true ;
    }catch(e){
        console.log("database error : " + e) ;
        return false ;
    }
}

function _getFavoriteNextMaxId(){

    var maxId = 1 ;
    var result = _doInTransaction(
        function tr(tx){
            var rs = tx.executeSql('SELECT sequence FROM Favorites order by sequence desc limit 1');
            if(rs.rows.length>0){
                maxId = parseInt(rs.rows.item(0).sequence) + 1 ;
            }
            console.debug("favorite maxid = " + maxId) ;
            _traceResultset(rs) ;
        }
    ) ;
    return maxId ;

}

function _traceResultset(rs){
    var str = "" ;
    if(typeof(rs) == 'undefined' || rs == 'undefined'){
        str = " typeof resultset is undefined!" ;
    }else{
        str = " ResultSet{rows:" + rs.rows.length + "]"

        str += "}" ;
        str += "\r\n" ;
        for(var i = 0; i < rs.rows.length; i++) {
            str += i + "> "
            var item = rs.rows.item(i) ;
            for(var prop in item ){
                str +=  prop + "=" + item[prop] + "|" ;
            }
            str += "\r\n" ;
        }
    }

    console.debug(str) ;
    return str ;
}

function _getNextSequence(tableName){

    var maxId = 1 ;
    var result = _doInTransaction(
        function tr(tx){
                var rs = tx.executeSql('SELECT sequence FROM ' + tableName + ' order by sequence desc limit 1');
            if(rs.rows.length>0){
                maxId = parseInt(rs.rows.item(0).sequence) + 1 ;
            }
            console.debug("favorite maxid = " + maxId) ;
            _traceResultset(rs) ;
        }
    ) ;
    return maxId ;

}

/**
  * js显示 confirmDialog
  */
function confirm(text){
    return true ;
}














/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
  * add A new Account to Database
  * @username
  * @password
  */
function addAccount(username , password){
    var sequence = _getNextSequence("Accounts") ;
    return _doInTransaction(
        function tr(tx){
            if(!isAccountExists(username)){
                var rs = tx.executeSql('INSERT INTO Accounts(sequence,username,password,isDefault) values (?,?,?,?)', [sequence,username,password,'F']);
                console.debug("addAccount execute : tx=" + tx + "params :" + username + "|" +password ) ;
                _traceResultset(rs) ;
            }
        }

    ) ;
}

function setDefaultAccount(username){
    return _doInTransaction(
        function tr(tx){

            var rs = tx.executeSql("UPDATE Accounts SET isDefault='T' WHERE username=?",[username]);
            console.debug("setDefaultAccount execute : tx=" + tx + "params :" + username ) ;
            _traceResultset(rs) ;

        }

    ) ;
}

function getDefaultAccount(){
    var account = null ;
    return _doInTransaction(
        function tr(tx){
            if(!isAccountExists(username)){
                var rs = tx.executeSql("SELECT * FROM Accounts WHERE isDefault='T')");
                console.debug("addAccount execute : tx=" + tx + " params :" + username ) ;
                _traceResultset(rs) ;
            }
        }

    ) ;
}

function isAccountExists(username){
    var exists = false ;
    _doInTransaction(
        function tr(tx){
            var rs = tx.executeSql('SELECT sequence FROM Accounts WHERE username=?', [username]);
            console.debug("isAccountExists execute : tx=" + tx + " params :" + username) ;
            _traceResultset(rs) ;
            for(var i = 0; i < rs.rows.length; i++) {
                exists = true ;
                break ;
            }
        }
    ) ;
    return exists ;
}

function deleteAccount(username){
    console.debug("deleteAccount  " + username) ;
    return _doInTransaction(
        function tr(tx){
        var rs = tx.executeSql('DELETE FROM Accounts WHERE username = ?',[username]);
            _traceResultset(rs) ;

        }
    ) ;
}

function clearAccounts(){
    console.debug("clearAccounts  execute" ) ;
    return _doInTransaction(
        function tr(tx){
            var rs = tx.executeSql('DELETE FROM Accounts');
            _traceResultset(rs) ;

        }
    ) ;
}

/**
 * Get all Accounts
 */
function getAllAccounts(){
    console.debug("Get all getAllAccounts  " ) ;
    var accounts = new Array();
    _doInTransaction(
        function tr(tx){
            var rs = tx.executeSql('SELECT * FROM Accounts');
            console.debug("getAllAccounts execute : tx=" + tx + " | ") ;
            for(var i = 0; i < rs.rows.length; i++) {
                var _item = new Object() ;
                _item.sequence = rs.rows.item(i).sequence ;
                _item.username = rs.rows.item(i).username ;
                _item.password = rs.rows.item(i).password ;
                _item.isDefault = rs.rows.item(i).isDefault ;

                accounts[i] = _item ;
            }
            _traceResultset(rs) ;

        }
    ) ;

    return accounts ;
}

/**
 * Get Account
 */
function getAccount(username){
    console.debug("getAccount  " +username) ;
    var account = null ;
    _doInTransaction(
        function tr(tx){
                    var rs = tx.executeSql('SELECT * FROM Accounts WHERE username = ?' , [username]);
            console.debug("getAllAccounts execute : tx=" + tx + " | ") ;
            for(var i = 0; i < rs.rows.length; i++) {
                var _item = new Object() ;
                _item.sequence = rs.rows.item(i).sequence ;
                _item.username = rs.rows.item(i).username ;
                _item.password = rs.rows.item(i).password ;
                _item.isDefault = rs.rows.item(i).isDefault ;

                account = _item ;
                break ;
            }
            _traceResultset(rs) ;

        }
    ) ;

    return account ;
}
